package com.yx.frame.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;

import com.yx.frame.jdbc.pool.PConnection;
import com.yx.frame.jdbc.pool.PoolManager;
import com.yx.frame.log.LogFactory;
import com.yx.frame.log.LogWriter;

import oracle.jdbc.OracleTypes;


public final class Sql {

	private LogWriter log = LogFactory.getInstance().getLogWriter();

	private String jndiName = "";

	private int MaxQueryRowCount = 5000;

	public Sql(String jndi) throws Exception {
		this.jndiName = jndi;
		if (jndiName == null || jndiName.trim().equals("")) {
			jndiName = PoolManager.getInstance().getDefaultDatabaseName();
		}
		this.MaxQueryRowCount = PoolManager.getInstance().getMaxQueryRowCount(
				jndiName);
	}

	public Sql() throws Exception {
		this(null);
	}

	public String getName() {
		return jndiName;
	}

	public int getMaxQueryRowCount() {
		return MaxQueryRowCount;
	}

	public void setMaxQueryRowCount(int maxQueryRowCount) {
		MaxQueryRowCount = maxQueryRowCount;
	}

	public boolean isConnected() {
		PConnection pConnection = null;
		try {
			pConnection = PoolManager.getInstance().getPoolConnection(
					jndiName);
			Connection conn = pConnection.getConnection();
			if (!conn.isClosed()) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			return false;
		} finally {
			close(null, null, pConnection);
		}
	}

	public Table query(SqlEntry sqlEntry) throws Exception {

		PConnection pConnection = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			pConnection = PoolManager.getInstance().getPoolConnection(
					jndiName);
			conn = pConnection.getConnection();
			stmt = conn.prepareStatement(sqlEntry.getSql());
			sqlEntry.copyParms(stmt, 0);
			log.debug(null, "SQL", sqlEntry.getSql());
			rs = stmt.executeQuery();
			ResultSetMetaData resultSetMetaData = rs.getMetaData();
			int colNumber = resultSetMetaData.getColumnCount();
			String[] colNames=new String[colNumber];
			Table table =null;
			int rowCount = 0;
			if (rs.next()) {
				Row row=new Row();
				for (int i = 1; i <= colNumber; i++) {
					colNames[i-1] = resultSetMetaData.getColumnName(i);
					String value=rs.getString(i);
					row.put(colNames[i-1], value==null?"":value);					
				}
				table=new Table(colNames);
				table.addRow(row);
				rowCount++;
				while (rs.next()) {
					row=new Row();
					for (int i = 1; i <= colNumber; i++) {
						String value=rs.getString(i);
						row.put(colNames[i-1], value==null?"":value);	
					}
					table.addRow(row);
					rowCount++;
					if (MaxQueryRowCount < rowCount) {
						break;
					}
				}				
			}else{
				for (int i = 1; i <= colNumber; i++) {
					colNames[i-1] = resultSetMetaData.getColumnName(i);									
				}
				table=new Table(colNames);
			}
			return table;
		} catch (Exception e) {
			throw new Exception("sql:[" + sqlEntry.getSql() + "]"
					+ e.getMessage());
		} finally {
			close(stmt, rs, pConnection);
			sqlEntry.closeClobReader();
		}
	}

	public int[] update(SqlEntry sqlEntry) throws Exception {
		PConnection pConnection = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			pConnection = PoolManager.getInstance().getPoolConnection(
					jndiName);
			conn = pConnection.getConnection();
			conn.setAutoCommit(false);
			stmt = conn.prepareStatement(sqlEntry.getSql());
			for (int rowNum = 0; rowNum < sqlEntry.getParmsRowCount(); rowNum++) {
				sqlEntry.copyParms(stmt, rowNum);
				stmt.addBatch();
			}
			log.debug(null, "SQL", sqlEntry.getSql());
			int[] ret = stmt.executeBatch();
			conn.commit();
			return ret;
		} catch (Exception e) {
			conn.rollback();
			throw new Exception("sql:[" + sqlEntry.getSql() + "]"
					+ e.getMessage());
		} finally {
			conn.setAutoCommit(true);
			close(stmt, rs, pConnection);
			sqlEntry.closeClobReader();
		}
	}

	// 2006/04/10
	public boolean function(String sql, Hashtable<String, String> parms)
			throws Exception {
		PConnection pConnection = null;
		Connection conn = null;
		CallableStatement callablestatement = null;
		try {
			pConnection = PoolManager.getInstance().getPoolConnection(
					jndiName);
			conn = pConnection.getConnection();
			callablestatement = conn.prepareCall(sql);
			if (parms != null) {
				Enumeration<String> enums = parms.keys();
				while (enums.hasMoreElements()) {
					String pKey = enums.nextElement();
					String value = parms.get(pKey);
					callablestatement.setString(pKey, value);
				}
			}
			log.debug(null, "SQL", sql);
			return callablestatement.execute();
		} catch (Exception e) {
			throw new Exception("sql:[" + sql + "]" + e.getMessage());
		} finally {
			close(callablestatement, null, pConnection);
		}
	}

	public List<int[]> update(List<SqlEntry> sqlEntryList) throws Exception {
		PConnection pConnection = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			List<int[]> retList = new ArrayList<int[]>();
			pConnection = PoolManager.getInstance().getPoolConnection(
					jndiName);
			conn = pConnection.getConnection();
			conn.setAutoCommit(false);
			for (int sIdx = 0; sIdx < sqlEntryList.size(); sIdx++) {
				SqlEntry sqlEntry = sqlEntryList.get(sIdx);
				stmt = conn.prepareStatement(sqlEntry.getSql());
				for (int rowNum = 0; rowNum < sqlEntry.getParmsRowCount(); rowNum++) {
					sqlEntry.copyParms(stmt, rowNum);
					stmt.addBatch();
				}
				log.debug(null, "SQL", sqlEntry.getSql());
				int[] ret = stmt.executeBatch();
				retList.add(ret);
				try {
					stmt.close();
				} catch (Exception ex) {
				}
			}
			conn.commit();
			return retList;
		} catch (Exception e) {
			//e.printStackTrace();
			conn.rollback();
			throw new Exception(e.getMessage());
		} finally {
			conn.setAutoCommit(true);
			close(stmt, rs, pConnection);
		}
	}
	/** 调用存储过程
	 * @param sqlEntry 
	 * @param flag 0-无返回结果 1-有返回结果
	 * @throws Exception
	 */
	public Table procedure(SqlEntry sqlEntry, int flag)
			throws Exception {
		PConnection pConnection = null;
		Connection conn = null;
		CallableStatement callablestatement = null;
		try {
			pConnection = PoolManager.getInstance().getPoolConnection(
					jndiName);
			conn = pConnection.getConnection();
			callablestatement = conn.prepareCall(sqlEntry.getSql());
			sqlEntry.copyParms(callablestatement, 0);
			if (flag==1) {
				int outIndex = sqlEntry.getParamType()!=null?sqlEntry.getParamType().length+1:1;
				callablestatement.registerOutParameter(outIndex, OracleTypes.CURSOR);
			}
			log.debug(null, "SQL", sqlEntry.getSql());
			callablestatement.execute();
			
			Table table =null;
			ResultSet rs = null;
			if (flag==1) {
				int outIndex = sqlEntry.getParamType()!=null?sqlEntry.getParamType().length+1:1;
				rs = (ResultSet)callablestatement.getObject(outIndex);
				ResultSetMetaData resultSetMetaData = rs.getMetaData();
				int colNumber = resultSetMetaData.getColumnCount();
				String[] colNames=new String[colNumber];
				int rowCount = 0;
				if (rs.next()) {
					Row row=new Row();
					for (int i = 1; i <= colNumber; i++) {
						colNames[i-1] = resultSetMetaData.getColumnName(i);
						row.put(colNames[i-1], rs.getString(i));					
					}
					table=new Table(colNames);
					table.addRow(row);
					rowCount++;
					while (rs.next()) {
						row=new Row();
						for (int i = 1; i <= colNumber; i++) {
							row.put(colNames[i-1], rs.getString(i));	
						}
						table.addRow(row);
						rowCount++;
						if (MaxQueryRowCount < rowCount) {
							break;
						}
					}				
				}else{
					for (int i = 1; i <= colNumber; i++) {
						colNames[i-1] = resultSetMetaData.getColumnName(i);									
					}
					table=new Table(colNames);
				}
			}
			
			return table;
		} catch (Exception e) {
			throw new Exception("sql:[" + sqlEntry.getSql() + "]"
					+ e.getMessage());
		} finally {
			close(callablestatement, null, pConnection);
			sqlEntry.closeClobReader();
		}
	}

	/**
	 * 存储过程 返回多个值包括集合、其他参数
	 * @param sqlEntry
	 * @param flag
	 * @param backParmType 返回的 数据类型
	 * @return
	 * @author dw
	 * @throws Exception
	 */
	public Table procedure(SqlEntry sqlEntry, int flag,int[] backParmType)
			throws Exception {
		PConnection pConnection = null;
		Connection conn = null;
		CallableStatement callablestatement = null;
		try {
			pConnection = PoolManager.getInstance().getPoolConnection(
					jndiName);
			conn = pConnection.getConnection();
			callablestatement = conn.prepareCall(sqlEntry.getSql());
			sqlEntry.copyParms(callablestatement, 0);
			if (flag==1) {
				int i = 0;
				for (int num : backParmType) {
					i++;
					int outIndex = sqlEntry.getParamType()!=null?sqlEntry.getParamType().length+i:1;
					callablestatement.registerOutParameter(outIndex, num);
				}
			}
			log.info(null,"SQL,",sqlEntry.getSql());
			callablestatement.execute();
			
			Table table =null;
			ResultSet rs = null;
			String[] str = new String[backParmType.length-1];
			if (flag==1) {
				for (int r = 0; r < backParmType.length; r++) {
					if(r!=backParmType.length-1){
						int outIndex = sqlEntry.getParamType()!=null?sqlEntry.getParamType().length+r+1:1;
						str[r] = String.valueOf(callablestatement.getObject(outIndex));
					}else{
						int outIndex = sqlEntry.getParamType()!=null?sqlEntry.getParamType().length+r+1:1;
						rs = (ResultSet)callablestatement.getObject(outIndex);
						ResultSetMetaData resultSetMetaData = rs.getMetaData();
						int colNumber = resultSetMetaData.getColumnCount();
						String[] colNames=new String[colNumber];
						int rowCount = 0;
						if (rs.next()) {
							Row row=new Row();
							for (int i = 1; i <= colNumber; i++) {
								colNames[i-1] = resultSetMetaData.getColumnName(i);
								row.put(colNames[i-1], rs.getString(i));					
							}
							table=new Table(colNames,str);
							table.addRow(row);
							rowCount++;
							while (rs.next()) {
								row=new Row();
								for (int i = 1; i <= colNumber; i++) {
									row.put(colNames[i-1], rs.getString(i));	
								}
								table.addRow(row);
								rowCount++;
								if (MaxQueryRowCount < rowCount) {
									log.info(null,"单次查询行数大于预设行数：",MaxQueryRowCount+"");
									break;
								}
							}				
						}else{
							for (int i = 1; i <= colNumber; i++) {
								colNames[i-1] = resultSetMetaData.getColumnName(i);									
							}
							table=new Table(colNames,str);
						}
					}
				}
				
			}
			return table;
		} catch (Exception e) {
			throw new Exception("sql:[" + sqlEntry.getSql() + "]"
					+ e.getMessage());
		} finally {
			close(callablestatement, null, pConnection);
			sqlEntry.closeClobReader();
		}
	}
	
	
	protected void close(Statement stmt, ResultSet rs,
			PConnection pConnection) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (Exception e1) {
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
				stmt = null;
			} catch (Exception e1) {
			}
		}
		if (pConnection != null) {
			try {
				pConnection.close();
				pConnection = null;
			} catch (Exception e) {
			}
		}
	}

}
